package com.regulareport.form.service.impl;

import com.regulareport.common.config.RuoYiConfig;
import com.regulareport.common.core.domain.entity.ReportIndexAdditionalRecording;
import com.regulareport.common.core.domain.entity.ReportIndexResult;
import com.regulareport.common.core.domain.entity.ReportTask;
import com.regulareport.common.core.domain.entity.SysDept;
import com.regulareport.common.enums.*;
import com.regulareport.common.utils.SecurityUtils;
import com.regulareport.common.utils.file.FileUploadUtils;
import com.regulareport.common.utils.poi.ExcelToJsonConverter3;
import com.regulareport.common.utils.poi.ExcelToJsonConverter4;
import com.regulareport.common.utils.poi.ExcelUtil1;
import com.regulareport.common.utils.poi.PoiUtil;
import com.regulareport.form.domain.db.result.FillingResult;
import com.regulareport.form.domain.rep.*;
import com.regulareport.form.domain.req.*;
import com.regulareport.form.mapper.*;
import com.regulareport.system.mapper.SysDeptMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
public class ReportFillingService {
    private static final Logger log = LoggerFactory.getLogger(ReportFillingService.class);
    private final ReportTaskMapper reportTaskMapper;
    private final HeadSubIndexMapMapper headSubIndexMapMapper;
    private final SysDeptMapper sysDeptMapper;
    private final ReportIndexResultMapper reportIndexResultMapper;
    private final ReportIndexAdditionalRecordingMapper reportIndexAdditionalRecordingMapper;
    private final String SUFFIX = "v";

    public ReportFillingService(ReportTaskMapper reportTaskMapper, HeadSubIndexMapMapper headSubIndexMapMapper, SysDeptMapper sysDeptMapper, ReportIndexResultMapper reportIndexResultMapper, ReportIndexAdditionalRecordingMapper reportIndexAdditionalRecordingMapper) {
        this.reportTaskMapper = reportTaskMapper;
        this.headSubIndexMapMapper = headSubIndexMapMapper;
        this.sysDeptMapper = sysDeptMapper;
        this.reportIndexResultMapper = reportIndexResultMapper;
        this.reportIndexAdditionalRecordingMapper = reportIndexAdditionalRecordingMapper;
    }


    @Transactional(rollbackFor = Throwable.class)
    public List<List<String>> importFile(MultipartFile file) throws Exception {
        List<String> deptNumError = new ArrayList<>();
        List<String> dataDateError = new ArrayList<>();
        List<String> gf1200Error = new ArrayList<>();
        List<List<String>> all = new ArrayList<>();
        all.add(deptNumError);
        all.add(dataDateError);
        all.add(gf1200Error);
        Long deptId = SecurityUtils.getLoginUser().getDeptId();
        ReportTaskFillingReq reportTaskFillingReq = new ReportTaskFillingReq();
        reportTaskFillingReq.setDelFlag(DelFlag.NORMAL.getCode());
        reportTaskFillingReq.setDeptId(deptId);
        reportTaskFillingReq.setEnable(EnableStatus.OK.getCode());
        reportTaskFillingReq.setTaskStatus(TaskStatus.DELIVERED.getCode());
        SysDept sysDept = sysDeptMapper.selectDeptById(deptId);
        List<ReportTaskFillingRep> reportTaskFillingReps = reportTaskMapper.listHandleReportTask(reportTaskFillingReq);
        if (reportTaskFillingReps == null || reportTaskFillingReps.isEmpty()) {
            throw new Exception("无待填报数据");
        }
        byte[] fileBytes = null;
        try {
            fileBytes = file.getBytes();
        } catch (Exception e) {
            log.error("文件模板转换失败", e);
            throw new Exception("文件模板转换失败");
        }
        if (fileBytes == null) {
            throw new Exception("文件模板无内容");
        }

        String uploadDir = RuoYiConfig.getMasterDataBackPath() + "/" + sysDept.getDeptNum() + "/back";
        String fileName = "";
        try {
            fileName = FileUploadUtils.uploadOriginalFile(uploadDir, file);
            log.info("总行模板{}，上传服务器成功", fileName);
        } catch (Exception e) {
            throw new Exception("模板上传服务器失败");
        }
        List<ReportTaskFillingRep> handleTask = new ArrayList<>();
        String absPath = uploadDir + "/" + file.getOriginalFilename();
        ReportTaskFillingRep gf1200 = null;
        Iterator<ReportTaskFillingRep> iterator = reportTaskFillingReps.iterator();
        while (iterator.hasNext()) {
            ReportTaskFillingRep reportTaskFillingRep = iterator.next();
            if ("GF1200".equalsIgnoreCase(reportTaskFillingRep.getReportFormNo())) {
                gf1200 = reportTaskFillingRep;
                gf1200.setGf1200Full(new ArrayList<>());
                iterator.remove();
                break;
            }
        }

        List<Map<String, String>> sheets = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(absPath);
             Workbook workbook = WorkbookFactory.create(fis)) {

            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                String sheetName = workbook.getSheetName(i);
                if ("TMP".equalsIgnoreCase(sheetName)) {
                    continue;
                }
                log.info("SheetName:{}", sheetName);
                String[] split1 = sheetName.split("_");
                String headSheetName = split1[0];
                String deptNum = split1[1];
                if (!sysDept.getDeptNum().equalsIgnoreCase(deptNum)) {
                    deptNumError.add(sheetName + "机构不匹配");
                    continue;
                }

                Map<String, String> map = new HashMap<>();
                map.put("H", headSheetName);
                map.put("F", sheetName);
                sheets.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("文件打开失败");
        }


        for (ReportTaskFillingRep reportTaskFillingRep : reportTaskFillingReps) {
            for (Map<String, String> sheet : sheets) {
                if (sheet.get("H").equalsIgnoreCase(reportTaskFillingRep.getSheetName())) {
                    if (reportTaskFillingRep.getMapEditionId() != null) {
                        reportTaskFillingRep.setSheetNameFull(sheet.get("F"));
                        handleTask.add(reportTaskFillingRep);
                    }
                    break;
                }
            }
        }

        if (gf1200 != null) {
            for (Map<String, String> sheet : sheets) {
                if (sheet.get("H").equalsIgnoreCase("G12CARD") || sheet.get("H").equalsIgnoreCase("G12-new-2022")
                        || sheet.get("H").equalsIgnoreCase("G12-per-2022")) {
                    gf1200.getGf1200Full().add(sheet.get("F"));
                }
            }
        }


        if (gf1200 != null && gf1200.getGf1200Full().size() == 3) {
            handleTask.add(gf1200);
        }

        if (gf1200 != null && gf1200.getGf1200Full().size() >= 1 && gf1200.getGf1200Full().size() < 3) {
            StringBuilder stringBuilder = new StringBuilder("GF1200总行表不全");
            for (int i = 0; i < gf1200.getGf1200Full().size(); i++) {
                if (i == 0) {
                    stringBuilder.append(", 已有数据:" + gf1200.getGf1200Full().get(i));
                } else {
                    stringBuilder.append(", 已有数据:" + "," + gf1200.getGf1200Full().get(i));
                }
            }
            gf1200Error.add(stringBuilder.toString());

        }

        if (handleTask.isEmpty()) {
            new Exception("没有处理的数据");
        }


        List<ReportIndexResult> reportIndexResults = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(absPath); Workbook headWorkbook = WorkbookFactory.create(fis);) {
            for (ReportTaskFillingRep task : handleTask) {
                if (task.getMapEditionId() == null) {
                    break;
                }

                List<SubCellDefinition> subCellDefinitions = headSubIndexMapMapper.selectSubCell(task.getReportId());
                Sheet sheet0 = null, sheet1 = null, sheet2 = null, sheet = null;
                if (task.getReportFormNo().equalsIgnoreCase("GF1200")) {
                    List<String> gf1200Full = task.getGf1200Full();
                    sheet0 = headWorkbook.getSheet(gf1200Full.get(0));
//                    if (!RegularUtil.checkDataDate(sheet0, task.getDataDate())) {
//                        dataDateError.add(gf1200Full.get(0) + " 数据日期不适配");
//                        continue;
//                    }
                    sheet1 = headWorkbook.getSheet(gf1200Full.get(1));
//                    if (!RegularUtil.checkDataDate(sheet1, task.getDataDate())) {
//                        dataDateError.add(gf1200Full.get(1) + " 数据日期不适配");
//                        continue;
//                    }
                    sheet2 = headWorkbook.getSheet(gf1200Full.get(2));
//                    if (!RegularUtil.checkDataDate(sheet2,  task.getDataDate())) {
//                        dataDateError.add(gf1200Full.get(2) + " 数据日期不适配");
//                        continue;
//                    }
                } else {
                    sheet = headWorkbook.getSheet(task.getSheetNameFull());
//                    if (!RegularUtil.checkDataDate(sheet, task.getDataDate())) {
//                        dataDateError.add(task.getSheetNameFull() + " 数据日期不适配");
//                        continue;
//                    }
                }
                String rootPath = RuoYiConfig.getHandlePath() + sysDept.getDeptNum()
                        + "/" + task.getDataDate() + "/";
                File file2 = new File(rootPath);
                if (!file2.exists()) {
                    file2.mkdirs();
                }
                Path target = Paths.get(rootPath + task.getFileName());
                String fileName1 = target.toFile().getAbsolutePath();
                int version = task.getHandleFileVersion() + 1;
                String name = SUFFIX + version;
                String s = addSuffixToFileName(fileName1, name);
                task.setHandleFileVersion(version);
                task.setHandleFilePath(s);

                List<HeadSubIndexMapFull> headSubIndexMapFulls = headSubIndexMapMapper.selectFull(task.getMapEditionId());
                List<SubCellDefinition> collecta = subCellDefinitions.stream()
                        .filter(e -> e.getCoordinateType() == CoordinateType.FILL.getCode())
                        .collect(Collectors.toList());
                List<SubCellDefinition> dateList = subCellDefinitions.stream()
                        .filter(e -> e.getCoordinateType() == CoordinateType.DATE.getCode())
                        .collect(Collectors.toList());
                List<SubCellDefinition> writeList = subCellDefinitions.stream()
                        .filter(e -> e.getCoordinateType() == CoordinateType.WRITER.getCode())
                        .collect(Collectors.toList());
                Map<Integer, List<SubCellDefinition>> subCellGroup = collecta.stream()
                        .collect(Collectors.groupingBy(e -> Integer.valueOf(e.getCellRow())));
                SimpleDateFormat YYYYMMDD = new SimpleDateFormat("yyyyMMdd");
                SimpleDateFormat YYYYZMMZDD = new SimpleDateFormat("yyyy年MM月dd日");

                try (FileInputStream subFis = new FileInputStream(Paths.get(RuoYiConfig.getProfile() + "/" + task.getFilePath()).toFile());
                     Workbook subWorkbook = WorkbookFactory.create(subFis);
                     FileOutputStream outputStream = new FileOutputStream(s)) {
                    subWorkbook.setForceFormulaRecalculation(true);
                    Sheet subSheet = subWorkbook.getSheetAt(0);
                    subSheet.setForceFormulaRecalculation(true);

                    if (task.getReportFormNo().equalsIgnoreCase("GF1200")) {
                        for (HeadSubIndexMapFull headSubIndexMapFull : headSubIndexMapFulls) {
                            int headRow = Integer.parseInt(headSubIndexMapFull.getHeadRow());
                            int subRow = Integer.parseInt(headSubIndexMapFull.getSubRow());
                            List<SubCellDefinition> subCellColList = subCellGroup.get(subRow);
                            log.info("subCellColList:{},subRow:{}", subCellColList, subRow);
                            if (subCellColList == null) continue;
                            for (SubCellDefinition subCellCol : subCellColList) {
                                if (subCellCol.getEditable()) {
                                    int cellCol = Integer.parseInt(subCellCol.getCellCol());
                                    Cell headCell0 = sheet0.getRow(headRow).getCell(cellCol);
                                    Cell headCell1 = sheet1.getRow(headRow).getCell(cellCol);
                                    Cell headCell2 = sheet2.getRow(headRow).getCell(cellCol);
                                    Cell subCell = subSheet.getRow(subRow).getCell(cellCol);

                                    CellType cellType = subCell.getCellType();
                                    log.info("headRow:{},cellType:{}", headRow, cellType);
                                    if (cellType == CellType.FORMULA) {
                                        continue;
                                    }
                                    String source0 = PoiUtil.getCellValueAsString(headCell0);
                                    String source1 = PoiUtil.getCellValueAsString(headCell1);
                                    String source2 = PoiUtil.getCellValueAsString(headCell2);
                                    BigDecimal bigDecimal0 = new BigDecimal("0");
                                    BigDecimal bigDecimal1 = new BigDecimal("0");
                                    BigDecimal bigDecimal2 = new BigDecimal("0");
                                    boolean hasV = false;
                                    try {
                                        bigDecimal0 = new BigDecimal(source0);
                                        hasV = true;
                                    } catch (Exception e) {
                                        e.printStackTrace();
                                    }
                                    try {
                                        bigDecimal1 = new BigDecimal(source1);
                                        hasV = true;
                                    } catch (Exception e) {
                                        e.printStackTrace();
                                    }
                                    try {
                                        bigDecimal2 = new BigDecimal(source2);
                                        hasV = true;
                                    } catch (Exception e) {
                                        e.printStackTrace();
                                    }
                                    log.info("source0:{}, source1:{}, source2:{}", source0, source1, source2);
                                    if (!hasV) {
                                        continue;
                                    }
                                    double v = bigDecimal0.add(bigDecimal1).add(bigDecimal2).doubleValue();
                                    if (subCellCol.getIndexName() != null && subCellCol.getIndexName().contains("户数")) {
                                        subCell.setCellValue((int) v);
                                    } else {
                                        subCell.setCellValue(v);
                                    }
                                }
                            }
                        }
                    } else {
                        for (HeadSubIndexMapFull headSubIndexMapFull : headSubIndexMapFulls) {
                            int headRow = Integer.parseInt(headSubIndexMapFull.getHeadRow());
                            int subRow = Integer.parseInt(headSubIndexMapFull.getSubRow());
                            List<SubCellDefinition> subCellColList = subCellGroup.get(subRow);
                            log.info("subCellColList:{}, subRow:{}", subCellColList, subRow);
                            if (subCellColList == null) continue;
                            for (SubCellDefinition subCellCol : subCellColList) {
                                if (subCellCol.getEditable()) {
                                    int cellCol = Integer.parseInt(subCellCol.getCellCol());
                                    Cell headCell;
                                    if ("GF0101b".equalsIgnoreCase(task.getReportFormNo())) {
                                        headCell = sheet.getRow(headRow).getCell(cellCol - 1);
                                    } else {
                                        headCell = sheet.getRow(headRow).getCell(cellCol);
                                    }

                                    Cell subCell = subSheet.getRow(subRow).getCell(cellCol);
                                    CellType cellType = subCell.getCellType();
                                    if (cellType == CellType.FORMULA) {
                                        continue;
                                    }
                                    String source = PoiUtil.getCellValueAsString(headCell);
                                    if (!StringUtils.hasText(source)) {
                                        continue;
                                    }
                                    if (subCellCol.getIndexName() != null && subCellCol.getIndexName().contains("户数")) {
                                        subCell.setCellValue((int) Double.parseDouble(source));
                                    } else {
                                        subCell.setCellValue(Double.parseDouble(source));
                                    }
                                }
                            }
                        }
                    }
                    if (dateList != null && !dateList.isEmpty()) {
                        SubCellDefinition subCellDefinition = dateList.get(0);
                        CellReference c = new CellReference(subCellDefinition.getCoordinate());
                        Cell cell = subSheet.getRow(c.getRow()).getCell(c.getCol());
                        String cellValueAsString = PoiUtil.getCellValueAsString(cell);
                        log.info("数据日期单元格内容：{}" + cellValueAsString);
                        Date parse = YYYYMMDD.parse(task.getDataDate());
                        String format = YYYYZMMZDD.format(parse);
                        if (StringUtils.hasLength(cellValueAsString)) {
                            cell.setCellValue(cellValueAsString + format);
                        } else {
                            cell.setCellValue(format);
                        }
                    }

                    if (writeList != null && !writeList.isEmpty()) {
                        SubCellDefinition subCellDefinition = writeList.get(0);
                        CellReference c = new CellReference(subCellDefinition.getCoordinate());
                        Cell cell = subSheet.getRow(c.getRow()).getCell(c.getCol());
                        String cellValueAsString = PoiUtil.getCellValueAsString(cell);
                        log.info("填表人单元格内容：{}" + cellValueAsString);
                        if (StringUtils.hasLength(cellValueAsString)) {
                            cell.setCellValue(cellValueAsString + SecurityUtils.getLoginUser().getUsername());
                        } else {
                            cell.setCellValue(SecurityUtils.getLoginUser().getUsername());
                        }
                    }
                    subWorkbook.write(outputStream);
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new Exception("数据打开失败");
                }


                try (FileInputStream subFis = new FileInputStream(s);
                     Workbook subWorkbook = WorkbookFactory.create(subFis)) {
                    Sheet subSheet = subWorkbook.getSheetAt(0);
                    FormulaEvaluator evaluator = subWorkbook.getCreationHelper().createFormulaEvaluator();
                    Date parse = YYYYMMDD.parse(task.getDataDate());
                    reportIndexResultMapper.deleteReportIndexResultByReportFormId(task.getReportId(), parse, sysDept.getDeptNum());
                    for (SubCellDefinition subCellCol : collecta) {
                        ReportIndexResult reportIndexResult = new ReportIndexResult();
                        int cellCol = Integer.parseInt(subCellCol.getCellCol());
                        int subRow = Integer.parseInt(subCellCol.getCellRow());
                        Cell subCell = subSheet.getRow(subRow).getCell(cellCol);
                        BigDecimal cellValueAsString = ExcelToJsonConverter3.getCellValueAsStringOri(subCell, evaluator, true);
                        reportIndexResult.setReportFormNo(subCellCol.getReportFormNo());
                        reportIndexResult.setReportFormID(task.getReportId());
                        reportIndexResult.setIndexNo(subCellCol.getIndexNo());
                        reportIndexResult.setIndexName(subCellCol.getIndexName());
                        reportIndexResult.setDepartNo(sysDept.getDeptNum());
                        reportIndexResult.setDepartName(sysDept.getDeptName());
                        reportIndexResult.setIndexOriginalValue(cellValueAsString);
                        reportIndexResult.setIndexSubmittedValue(cellValueAsString);
                        reportIndexResult.setReviseDate(new Date(System.currentTimeMillis()));
                        reportIndexResult.setDataDate(parse);
                        reportIndexResults.add(reportIndexResult);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new Exception("数据打开失败");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据打开失败");
        }

        List<ReportIndexResult> list = new ArrayList<>();
        for (int i = 0; i < reportIndexResults.size(); i++) {
            list.add(reportIndexResults.get(i));
            if (list.size() >= 500) {
                reportIndexResultMapper.insertReportIndexResultBatch(list);
                list.clear();
            }
        }
        if (!list.isEmpty()) {
            reportIndexResultMapper.insertReportIndexResultBatch(list);
            list.clear();
        }

        for (ReportTaskFillingRep task : handleTask) {
            task.setUpdateBy(SecurityUtils.getLoginUser().getUsername());
            List<HashMap<String, Object>> list1 = reportIndexAdditionalRecordingMapper.selectCount(task.getReportFormNo(), sysDept.getDeptNum());
            TaskStatus taskStatus = TaskStatus.VERIFY;
            for (HashMap<String, Object> stringIntegerHashMap : list1) {
                String a = String.valueOf(stringIntegerHashMap.get("a"));
                String c = String.valueOf(stringIntegerHashMap.get("c"));
                if ("b".equalsIgnoreCase(a) && !"0".equalsIgnoreCase(c)) {
                    taskStatus = TaskStatus.IMPORT;
                    break;
                }

                if ("l".equalsIgnoreCase(a) && !"0".equalsIgnoreCase(c)) {
                    taskStatus = TaskStatus.BL;
                }
            }
            task.setTaskStatus(taskStatus.getCode());
            task.setJsonState(FileJsonState.U.getCode());
            reportTaskMapper.reportFilling(task);
        }

        return all;
    }

    public String addSuffixToFileName(String fileName, String suffix) {
        int lastDotIndex = fileName.lastIndexOf('.');
        if (lastDotIndex != -1) {
            return fileName.substring(0, lastDotIndex) + suffix + fileName.substring(lastDotIndex);
        } else {
            return fileName + suffix;
        }
    }

    public ReportFillingQuery1Rep query(ReportFillingQueryReq req) throws Exception {
        ReportTask reportTask = new ReportTask();
        reportTask.setTaskId(Long.valueOf(req.getTaskId()));
        ReportFillingQueryRep b = reportTaskMapper.findFilling(reportTask);
        if (b == null) {
            throw new Exception("没有记录");
        }
        try {
            ReportFillingQuery1Rep a = new ReportFillingQuery1Rep();
            a.setTaskId(b.getTaskId());
            a.setMapEditionId(b.getMapEditionId());
            a.setReportId(b.getReportId());
            a.setReportNo(b.getReportNo());
            a.setReportName(b.getReportName());
            a.setSheetName(b.getSheetName());
            a.setEdition(b.getEdition());
            a.setHandleFileVersion(b.getHandleFileVersion());
            a.setTaskStatus(b.getTaskStatus());
            a.setUpdateBy(b.getUpdateBy());
            a.setUpdateTime(b.getUpdateTime());
            a.setCreateBy(b.getCreateBy());
            a.setCreateTime(b.getCreateTime());
            if (b.getJsonState() == FileJsonState.U.getCode()) {
                String s;
                if (TaskStatus.DELIVERED.getCode().equalsIgnoreCase(b.getTaskStatus())) {
                    s = ExcelToJsonConverter3.convertExcelWithStyles(
                            Paths.get(RuoYiConfig.getProfile() + "/" + b.getFilePath()), true, true, false);
                    a.setFileJson(s);
                } else {
                    s = ExcelToJsonConverter3.convertExcelWithStyles(
                            Paths.get(b.getHandleFilePath()), true, true, true);
                    a.setFileJson(s);
                }

                b.setJsonState(FileJsonState.D.getCode());
                b.setFileJson(s);
                reportTaskMapper.updateJson(b);
            } else {
                a.setFileJson(b.getFileJson());
            }

            return a;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("文件转换失败");
        }
    }

    public Path export(ReportFillingQueryReq req) throws Exception {
        ReportTask reportTask = new ReportTask();
        reportTask.setTaskId(Long.valueOf(req.getTaskId()));
        ReportFillingQueryRep b = reportTaskMapper.findFilling(reportTask);
        if (b == null) {
            throw new Exception("文件不存在");
        }
        if (!StringUtils.hasText(b.getHandleFilePath())) {
            return Paths.get(b.getFilePath());
        }
        String handleFilePath = b.getHandleFilePath();
        return Paths.get(handleFilePath);
    }

    public Path batchExport(ReportFillingQueryBatchReq req) {
        if (req.getTaskId() == null || req.getTaskId().isEmpty()) {
            return null;
        }

        List<ReportTask> reportTasks = new ArrayList<>();
        for (String s : req.getTaskId()) {
            ReportTask reportTask = new ReportTask();
            reportTask.setTaskId(Long.valueOf(s));
        }
        List<ReportFillingQueryRep> b = reportTaskMapper.findFillingBatch(reportTasks);
        if (b == null || b.isEmpty()) {
            return null;
        }
        return null;
    }

    @Transactional(rollbackFor = Throwable.class)
    public void back(BackReq req, MultipartFile file) throws Exception {
        if (req.getTaskIds() == null || req.getTaskIds().isEmpty()) {
            throw new Exception("未选择补录任务");
        }

        BackSearch backSearch = new BackSearch();
        backSearch.setDeptId(SecurityUtils.getLoginUser().getDeptId());
        backSearch.setTaskIds(new ArrayList<>());
        for (String taskId : req.getTaskIds()) {
            backSearch.getTaskIds().add(Long.valueOf(taskId));
        }
        SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getDeptId());

        List<BackSearchRe> backSearchRes = reportTaskMapper.findBack(backSearch);
        if (backSearchRes == null || backSearchRes.isEmpty()) {
            throw new Exception("无补录任务");
        }

        List<BackSearchRe> collect = backSearchRes.stream().filter(e -> !TaskStatus.IMPORT.getCode().equalsIgnoreCase(e.getTaskStatus())).collect(Collectors.toList());

        if (!collect.isEmpty()) {
            throw new Exception("不能选择非补录任务");
        }

        ExcelUtil1<ReportIndexAdditionalRecording> util = new ExcelUtil1<>(ReportIndexAdditionalRecording.class);
        List<ReportIndexAdditionalRecording> recordings = util.importExcel(file.getInputStream());
        List<ReportIndexAdditionalRecording> collect1 = recordings.parallelStream().filter(Objects::nonNull).collect(Collectors.toList());
        if (!collect1.isEmpty()) {
            String username = SecurityUtils.getLoginUser().getUsername();
            collect1.parallelStream().forEach(e -> {
                        e.setCreateBy(username);
                        e.setUpdateBy(username);
                        if (e.getProcessType().equalsIgnoreCase(BLStatus.RATE.getCode())) {
                            e.setCurrencyCode("840_T01");
                        }
                    }
            );

            reportIndexAdditionalRecordingMapper.insertAdditionalBatch(collect1);
        }

        doBack(backSearchRes, sysDept);
    }

    @Transactional(rollbackFor = Throwable.class)
    public BigDecimal getBigDecimal(FillingResult recording, String cellValueAsString, Map<String, BigDecimal> m) throws Exception {
        BigDecimal result = null;
        if (BLStatus.PLUS.getCode().equals(recording.getProcessType())) {
            BigDecimal bigDecimal = new BigDecimal(cellValueAsString);
            result = bigDecimal.add(recording.getIndexValue());
        } else if (BLStatus.MINUS.getCode().equals(recording.getProcessType())) {
            BigDecimal bigDecimal = new BigDecimal(cellValueAsString);
            result = bigDecimal.subtract(recording.getIndexValue());
        } else if (BLStatus.REPLACE.getCode().equals(recording.getProcessType())) {
            result = recording.getIndexValue();
        } else if (BLStatus.RATE.getCode().equals(recording.getProcessType())) {
            BigDecimal r;
            if (m.containsKey(recording.getCurrencyCode())) {
                r = m.get(recording.getCurrencyCode());
            } else {
                String[] split = recording.getCurrencyCode().split("_");
                r = reportIndexAdditionalRecordingMapper.selectRate(split[0], split[1], recording.getDataDt());
                if (r == null) {
                    throw new Exception("汇率未配置");
                }
                m.put(recording.getCurrencyCode(), r);
            }
            result = recording.getIndexValue2().multiply(r).add(recording.getIndexValue());
        } else if (BLStatus.C0.getCode().equals(recording.getProcessType())) {
            return new BigDecimal("0");
        }
        return result;
    }


    @Transactional(rollbackFor = Throwable.class)
    public void bl(BackReq req) throws Exception {
        if (req.getTaskIds() == null || req.getTaskIds().isEmpty()) {
            throw new Exception("未选择补零任务");
        }

        BackSearch backSearch = new BackSearch();
        backSearch.setDeptId(SecurityUtils.getLoginUser().getDeptId());
        SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getDeptId());
        backSearch.setTaskIds(new ArrayList<>());
        for (String taskId : req.getTaskIds()) {
            backSearch.getTaskIds().add(Long.valueOf(taskId));
        }
        List<BackSearchRe> backSearchRes = reportTaskMapper.findBack(backSearch);
        if (backSearchRes == null || backSearchRes.isEmpty()) {
            throw new Exception("无补零任务");
        }

        List<BackSearchRe> collect = backSearchRes.stream().filter(e -> !TaskStatus.BL.getCode().equalsIgnoreCase(e.getTaskStatus())).collect(Collectors.toList());

        if (!collect.isEmpty()) {
            throw new Exception("不能选择非补零任务");
        }

        doBl(backSearchRes, sysDept);
    }

    @Transactional(rollbackFor = Throwable.class)
    public void rollback(BackReq req) throws Exception {
        if (req.getTaskIds() == null || req.getTaskIds().isEmpty()) {
            throw new Exception("未选择回退任务");
        }
        SimpleDateFormat YYYYMMDD = new SimpleDateFormat("yyyyMMdd");
        BackSearch backSearch = new BackSearch();
        backSearch.setDeptId(SecurityUtils.getLoginUser().getDeptId());
        backSearch.setTaskIds(new ArrayList<>());
        for (String taskId : req.getTaskIds()) {
            backSearch.getTaskIds().add(Long.valueOf(taskId));
        }

        List<BackSearchRe> backSearchRes = reportTaskMapper.findBack(backSearch);
        SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getDeptId());
        for (BackSearchRe task : backSearchRes) {
            Date parse = YYYYMMDD.parse(task.getDataDate());
            reportIndexResultMapper.deleteReportIndexResultByReportFormId(task.getReportId(), parse, sysDept.getDeptNum());
            for (int i = 1; i <= task.getFileVersion(); i++) {
                Path target = Paths.get(task.getHandleFilePath());
                String name = SUFFIX + i;
                String s = addSuffixToFileName(task.getFileName(), name);
                s = target.getParent().toFile().getAbsolutePath() + "/" + s;
                File file = new File(s);
                if (file.exists()) {
                    file.delete();
                }
            }
        }

        List<Long> re = new ArrayList<>();
        for (String taskId : req.getTaskIds()) {
            re.add(Long.valueOf(taskId));
        }

        reportTaskMapper.rollback(re, SecurityUtils.getLoginUser().getUsername());
    }

    @Transactional(rollbackFor = Throwable.class)
    public void doBack(List<BackSearchRe> backSearchRes, SysDept sysDept) throws Exception {
        Map<String, BigDecimal> map = new HashMap<>();
        List<ReportIndexResult> reportIndexResults = new ArrayList<>();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
        for (BackSearchRe backSearchRe : backSearchRes) {
            String dataDate = backSearchRe.getDataDate();
            Date parse = simpleDateFormat.parse(dataDate);
            backSearchRe.setDddd(parse);
            backSearchRe.setDeptNum(sysDept.getDeptNum());

            Path target = Paths.get(backSearchRe.getHandleFilePath());
            int version = backSearchRe.getFileVersion() + 1;
            String name = SUFFIX + version;
            String s = addSuffixToFileName(backSearchRe.getFileName(), name);
            s = target.getParent().toFile().getAbsolutePath() + "/" + s;
            List<FillingResult> recordings = reportIndexAdditionalRecordingMapper.selectBySearch(backSearchRe);
            if (recordings.isEmpty()) continue;

            try (FileInputStream subFis = new FileInputStream(backSearchRe.getHandleFilePath());
                 Workbook subWorkbook = WorkbookFactory.create(subFis);
                 FileOutputStream outputStream = new FileOutputStream(s)) {
                subWorkbook.setForceFormulaRecalculation(true);
                Sheet sheetAt = subWorkbook.getSheetAt(0);
                sheetAt.setForceFormulaRecalculation(true);
                for (FillingResult recording : recordings) {
                    Cell cell = sheetAt.getRow(recording.getCellRow()).getCell(recording.getCellCol());
                    String cellValueAsString = PoiUtil.getCellValueAsString(cell);
                    BigDecimal result= getBigDecimal(recording, cellValueAsString, map);
                    if (result != null) {
                        cell.setCellValue(result.doubleValue());
                    }
                }

                subWorkbook.write(outputStream);
            } catch (Exception e) {
                e.printStackTrace();
                if ("汇率未配置".equalsIgnoreCase(e.getMessage())) {
                    throw e;
                }

                throw new Exception("补录异常");
            }
            //更新记录表
            ReportTaskFillingRep task = new ReportTaskFillingRep();
            task.setTaskId(backSearchRe.getTaskId());
            task.setUpdateBy(SecurityUtils.getLoginUser().getUsername());
            task.setHandleFilePath(s);
            task.setHandleFileVersion(version);
            task.setJsonState(FileJsonState.U.getCode());

            List<SubCellDefinition> subCellDefinitions = headSubIndexMapMapper.selectSubCellFilling(backSearchRe.getReportId());
            Map<String, FillingResult> idToNameMap = recordings.stream()
                    .collect(Collectors.toMap(FillingResult::getIndexNo, Function.identity()));
            try (FileInputStream subFis = new FileInputStream(s);
                 Workbook subWorkbook = WorkbookFactory.create(subFis)) {
                Sheet subSheet = subWorkbook.getSheetAt(0);
                FormulaEvaluator evaluator = subWorkbook.getCreationHelper().createFormulaEvaluator();
                for (SubCellDefinition subCellCol : subCellDefinitions) {
                    ReportIndexResult reportIndexResult = new ReportIndexResult();
                    int cellCol = Integer.parseInt(subCellCol.getCellCol());
                    int subRow = Integer.parseInt(subCellCol.getCellRow());
                    Cell subCell = subSheet.getRow(subRow).getCell(cellCol);
                    BigDecimal cellValueAsString = ExcelToJsonConverter3.getCellValueAsStringOri(subCell, evaluator, true);
                    reportIndexResult.setReportFormNo(subCellCol.getReportFormNo());
                    reportIndexResult.setReportFormID(backSearchRe.getReportId());
                    reportIndexResult.setIndexNo(subCellCol.getIndexNo());
                    reportIndexResult.setIndexName(subCellCol.getIndexName());
                    reportIndexResult.setDepartNo(sysDept.getDeptNum());
                    reportIndexResult.setDepartName(sysDept.getDeptName());
                    reportIndexResult.setIndexSubmittedValue(cellValueAsString);
                    FillingResult fillingResult = idToNameMap.get(subCellCol.getIndexNo());
                    if (fillingResult != null) {
                        reportIndexResult.setIndexSupplementValue(fillingResult.getIndexValue());
                    }
                    reportIndexResult.setReviseDate(new Date(System.currentTimeMillis()));
                    reportIndexResult.setDataDate(parse);
                    reportIndexResults.add(reportIndexResult);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new Exception("数据打开失败");
            }


            int a = reportIndexAdditionalRecordingMapper.selectCountB0(backSearchRe.getReportNo(), sysDept.getDeptNum());
            TaskStatus taskStatus = TaskStatus.VERIFY;
            if (a > 0) {
                taskStatus = TaskStatus.BL;
            }
            task.setTaskStatus(taskStatus.getCode());
            reportTaskMapper.reportFilling(task);
        }

        List<ReportIndexResult> list = new ArrayList<>();
        for (int i = 0; i < reportIndexResults.size(); i++) {
            list.add(reportIndexResults.get(i));
            if (list.size() >= 500) {
                reportIndexResultMapper.insertBLReportIndexResultBatch(list);
                list.clear();
            }
        }
        if (!list.isEmpty()) {
            reportIndexResultMapper.insertBLReportIndexResultBatch(list);
            list.clear();
        }
    }

    @Transactional(rollbackFor = Throwable.class)
    public void doBl(List<BackSearchRe> backSearchRes, SysDept sysDept) throws Exception {
        List<ReportIndexResult> reportIndexResults = new ArrayList<>();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
        for (BackSearchRe backSearchRe : backSearchRes) {
            String dataDate = backSearchRe.getDataDate();
            Date parse = simpleDateFormat.parse(dataDate);
            backSearchRe.setDddd(parse);
            backSearchRe.setDeptNum(sysDept.getDeptNum());

            Path target = Paths.get(backSearchRe.getHandleFilePath());
            int version = backSearchRe.getFileVersion() + 1;
            String name = SUFFIX + version;
            String s = addSuffixToFileName(backSearchRe.getFileName(), name);
            s = target.getParent().toFile().getAbsolutePath() + "/" + s;
            List<FillingResult> recordings = reportIndexAdditionalRecordingMapper.selectByB0Search(backSearchRe);
            if (recordings.isEmpty()) continue;
            try (FileInputStream subFis = new FileInputStream(backSearchRe.getHandleFilePath());
                 Workbook subWorkbook = WorkbookFactory.create(subFis);
                 FileOutputStream outputStream = new FileOutputStream(s)) {
                subWorkbook.setForceFormulaRecalculation(true);
                Sheet sheetAt = subWorkbook.getSheetAt(0);
                sheetAt.setForceFormulaRecalculation(true);
                for (FillingResult recording : recordings) {
                    Cell cell = sheetAt.getRow(recording.getCellRow()).getCell(recording.getCellCol());
                    String cellValueAsString = PoiUtil.getCellValueAsString(cell);
                    if (!StringUtils.hasText(cellValueAsString)) {
                        cell.setCellValue(0);
                    }
                }

                subWorkbook.write(outputStream);
            } catch (Exception e) {
                log.error("补零异常", e);
                throw new Exception("补零异常");
            }
            //更新记录表
            ReportTaskFillingRep task = new ReportTaskFillingRep();
            task.setTaskId(backSearchRe.getTaskId());
            task.setUpdateBy(SecurityUtils.getLoginUser().getUsername());
            task.setHandleFilePath(s);
            task.setHandleFileVersion(version);
            task.setJsonState(FileJsonState.U.getCode());

            List<SubCellDefinition> subCellDefinitions = headSubIndexMapMapper.selectSubCellFilling(backSearchRe.getReportId());
            Map<String, FillingResult> idToNameMap = recordings.stream()
                    .collect(Collectors.toMap(FillingResult::getIndexNo, Function.identity()));
            try (FileInputStream subFis = new FileInputStream(s);
                 Workbook subWorkbook = WorkbookFactory.create(subFis)) {
                Sheet subSheet = subWorkbook.getSheetAt(0);
                FormulaEvaluator evaluator = subWorkbook.getCreationHelper().createFormulaEvaluator();
                for (SubCellDefinition subCellCol : subCellDefinitions) {
                    ReportIndexResult reportIndexResult = new ReportIndexResult();
                    int cellCol = Integer.parseInt(subCellCol.getCellCol());
                    int subRow = Integer.parseInt(subCellCol.getCellRow());
                    Cell subCell = subSheet.getRow(subRow).getCell(cellCol);
                    BigDecimal cellValueAsString = ExcelToJsonConverter3.getCellValueAsStringOri(subCell, evaluator, true);
                    reportIndexResult.setReportFormNo(subCellCol.getReportFormNo());
                    reportIndexResult.setReportFormID(backSearchRe.getReportId());
                    reportIndexResult.setIndexNo(subCellCol.getIndexNo());
                    reportIndexResult.setIndexName(subCellCol.getIndexName());
                    reportIndexResult.setDepartNo(sysDept.getDeptNum());
                    reportIndexResult.setDepartName(sysDept.getDeptName());
                    reportIndexResult.setIndexSubmittedValue(cellValueAsString);
                    FillingResult fillingResult = idToNameMap.get(subCellCol.getIndexNo());
                    if (fillingResult != null && cellValueAsString != null) {
                        reportIndexResult.setIndexBlValue(new BigDecimal("0"));
                    }
                    reportIndexResult.setReviseDate(new Date(System.currentTimeMillis()));
                    reportIndexResult.setDataDate(parse);
                    reportIndexResults.add(reportIndexResult);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new Exception("数据打开失败");
            }

            TaskStatus taskStatus = TaskStatus.VERIFY;
            task.setTaskStatus(taskStatus.getCode());
            reportTaskMapper.reportFilling(task);
        }

        List<ReportIndexResult> list = new ArrayList<>();
        for (ReportIndexResult reportIndexResult : reportIndexResults) {
            list.add(reportIndexResult);
            if (list.size() >= 500) {
                reportIndexResultMapper.insertB0ReportIndexResultBatch(list);
                list.clear();
            }
        }
        if (!list.isEmpty()) {
            reportIndexResultMapper.insertB0ReportIndexResultBatch(list);
            list.clear();
        }
    }

    @Transactional(rollbackFor = Throwable.class)
    public void backAll() throws Exception {
        SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getDeptId());

        List<BackSearchRe> backSearchRes = reportTaskMapper.findBackAll(sysDept.getDeptId(), TaskStatus.IMPORT.getCode());
        if (backSearchRes == null || backSearchRes.isEmpty()) {
            throw new Exception("无补录任务");
        }
        doBack(backSearchRes, sysDept);
    }

    @Transactional(rollbackFor = Throwable.class)
    public void blAll() throws Exception {
        SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getDeptId());

        List<BackSearchRe> backSearchRes = reportTaskMapper.findBackAll(sysDept.getDeptId(), TaskStatus.BL.getCode());
        if (backSearchRes == null || backSearchRes.isEmpty()) {
            throw new Exception("无补录任务");
        }
        doBl(backSearchRes, sysDept);
    }

//    public void importRecordings(List<ReportIndexAdditionalRecording> recordings) {
//        String username = SecurityUtils.getLoginUser().getUsername();
//        SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getDeptId());
//        recordings.stream().forEach(e -> {
//            e.setCreateBy(username);
//            e.setDeptNum(sysDept.getDeptNum());
//        });
//    }

    public FillingEditRep edit(FillingEditReq req) throws Exception {
        ReportTask reportTask = new ReportTask();
        reportTask.setTaskId(Long.valueOf(req.getTaskId()));
        BackSearchRe backSearchRe = reportTaskMapper.findEdit(reportTask);
        if (backSearchRe == null) {
            throw new Exception("没有记录");
        }

        FillingEditRep result = new FillingEditRep();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
        try {
            if (FileJsonState.U.getCode() == backSearchRe.getJsonState()) {
                String json;
                String dataDate = backSearchRe.getDataDate();
                Date parse = simpleDateFormat.parse(dataDate);
                backSearchRe.setDddd(parse);
                SysDept sysDept = sysDeptMapper.selectDeptById(SecurityUtils.getLoginUser().getDeptId());
                backSearchRe.setDeptNum(sysDept.getDeptNum());
                List<FillingResult> recordings = reportIndexAdditionalRecordingMapper.selectAllBuLu(backSearchRe);
                Set<String> nameCitySet = recordings.stream()
                        .map(e -> e.getCellRow() + "-" + e.getCellCol()) // 组合属性
                        .collect(Collectors.toSet());
                if (TaskStatus.DELIVERED.getCode().equalsIgnoreCase(backSearchRe.getTaskStatus())) {
                    json = ExcelToJsonConverter4.convertExcelWithStyles(
                            Paths.get(RuoYiConfig.getProfile() + "/" + backSearchRe.getFilePath()), true, true, false, nameCitySet);
                } else {
                    json = ExcelToJsonConverter4.convertExcelWithStyles(
                            Paths.get(backSearchRe.getHandleFilePath()), true, true, true, nameCitySet);
                }

                result.setJson(json);
                backSearchRe.setFileJson(json);
                backSearchRe.setJsonState(FileJsonState.D.getCode());
                reportTaskMapper.updateEditJson(backSearchRe);
            } else {
                result.setJson(backSearchRe.getFileJson());
            }

            return result;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("文件转换失败");
        }
    }
}
